﻿create PROCEDURE Sync.SP_Settings_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Settings_Temp TABLE 
		(
				[Key] nvarchar(50),
				[Value] nvarchar(50),
				[CommitteeID] uniqueidentifier,
				[ID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Settings_Temp 
	   SELECT	
                Tbl.Col.value('Key[1]', 'nvarchar(50)') [Key],
				Tbl.Col.value('Value[1]', 'nvarchar(50)') [Value],
				Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
				Tbl.Col.value('ID[1]', 'uniqueidentifier') [ID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Settings') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Settings] ([Key],[Value],[CommitteeID],[ID])
	SELECT t.[Key],t.[Value],t.[CommitteeID],t.[ID]
	FROM  @Settings_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Settings_Tracking s
        WHERE t.[ID] = s.[ID]
        
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Settings]
	SET [Settings].[Key] = t.[Key],
		[Settings].[Value] = t.[Value],	
		[Settings].[CommitteeID] = t.[CommitteeID],
		[Settings].[ID] = t.[ID] 
	FROM [dbo].[Settings] s JOIN @Settings_Temp t 
    ON s.[ID] = t.[ID]
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Settings_Tracking r
				  WHERE t.[ID] = r.[ID] )
                 ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Settings]
    FROM @Settings_Temp t JOIN [dbo].[Settings] s
    ON t.[ID] = s.[ID]
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END 

GO
